using Microsoft.AspNetCore.Mvc;
using 后端服务.Models;
using 后端服务.Services;

namespace 后端服务.Controllers;

[ApiController]
[Route("api/[controller]")]
public class SqlDemoController : ControllerBase
{
    private readonly SqlQueryService _sqlQueryService;

    public SqlDemoController(SqlQueryService sqlQueryService)
    {
        _sqlQueryService = sqlQueryService;
    }

    [HttpGet("users")]
    public async Task<IActionResult> GetUsers()
    {
        try
        {
            // 示例1：简单查询
            var sql = "SELECT * FROM Users";
            var users = await _sqlQueryService.QueryAsync<User>(sql);
            return Ok(users);
        }
        catch (Exception ex)
        {
            return StatusCode(500, ex.Message);
        }
    }

    [HttpGet("user/{username}")]
    public async Task<IActionResult> GetUserByUsername(string username)
    {
        try
        {
            // 示例2：参数化查询
            var sql = "SELECT * FROM Users WHERE Username = :username";
            var parameters = new Dictionary<string, object>
            {
                { ":username", username }
            };
            
            var user = await _sqlQueryService.QueryAsync<User>(sql, parameters);
            return Ok(user.FirstOrDefault());
        }
        catch (Exception ex)
        {
            return StatusCode(500, ex.Message);
        }
    }

    [HttpGet("count")]
    public async Task<IActionResult> GetUserCount()
    {
        try
        {
            // 示例3：获取标量值
            var sql = "SELECT COUNT(*) FROM Users";
            var count = await _sqlQueryService.ExecuteScalarAsync<int>(sql);
            return Ok(new { Count = count });
        }
        catch (Exception ex)
        {
            return StatusCode(500, ex.Message);
        }
    }

    [HttpPost("query")]
    public async Task<IActionResult> ExecuteQuery([FromBody] SqlQueryRequest request)
    {
        try
        {
            // 示例4：执行动态SQL查询
            var result = await _sqlQueryService.QueryDynamicAsync(request.Sql, request.Parameters);
            return Ok(result);
        }
        catch (Exception ex)
        {
            return StatusCode(500, ex.Message);
        }
    }
}

public class SqlQueryRequest
{
    public string Sql { get; set; } = string.Empty;
    public Dictionary<string, object>? Parameters { get; set; }
}
